﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ebiz.libs;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using Ionic.Zip;
using System.Drawing;

namespace Ebiz
{
    public partial class DownloadCL : System.Web.UI.Page
    {
        public string testtxt = "";
        public string CLNumber = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) {
                if (Request.QueryString["dlcl"] != null &&
                        !(string.IsNullOrEmpty(Request.QueryString["dlcl"])))
                {
                    string CLNum = Request.QueryString["dlcl"].ToString();
                    CLNumber = CLNum;
                    string MainTable = GenerateTable(CLNum);
                    DownloadZIP(CLNum);
                }
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }

        private static Dictionary<string, string> LoadCLData(string CLNum) {
            Dictionary<string, string> result = new Dictionary<string, string>();

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.CLNum, " +
                "CONVERT(varchar,tbl_Request.DateRequest, 105) AS DateRequest, " +
                "tbl_Request.DistributorCode, " +
                "mstDistributor.DistributorName, " +
                "mstDistributor.PIC, " +
                "mstDistributor.PICName, " +
                "mstDistributor.Address, " +
                "tbl_Request.CLCustName, " +
                "tbl_Request.Token, " +
                "CONVERT(varchar,tbl_Request.DateStart,105) AS DateStart, " + 
                "CONVERT(varchar,tbl_Request.DateEnd,105) AS DateEnd, " +
                "tbl_Request.DiscType, " +
                "tbl_Request.PrevCL " +
                "FROM " + dbName + ".dbo.tbl_Request " +
                "INNER JOIN " + dbName + ".dbo.mstDistributor ON mstDistributor.DistributorCode = tbl_Request.DistributorCode " +
                "WHERE (tbl_Request.CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            string DateRequest = rdr.GetValue(1).ToString();
                            string DistributorCode = rdr.GetValue(2).ToString();
                            string DistributorName = rdr.GetValue(3).ToString();
                            string DistributorPIC = rdr.GetValue(4).ToString();
                            string DistributorPICName = rdr.GetValue(5).ToString();
                            string DistributorAddr = rdr.GetValue(6).ToString();
                            string CLCustName = rdr.GetValue(7).ToString();
                            string Token = rdr.GetValue(8).ToString();
                            string Link = "id=" + CLNum + "&atdn=" + Token;
                            string EncLink = CryptLib.Base64(Link, CryptMode.ENCRYPT);
                            string DateStart = rdr.GetValue(9).ToString();
                            string DateEnd = rdr.GetValue(10).ToString();
                            string DiscType = rdr.GetValue(11).ToString();
                            string PrevCL = rdr.GetValue(12).ToString();

                            result.Add("DateRequest", DateRequest);
                            result.Add("DistributorCode", DistributorCode);
                            result.Add("DistributorName", DistributorName);
                            result.Add("DistributorPIC", DistributorPIC);
                            result.Add("DistributorPICName", DistributorPICName);
                            result.Add("DistributorAddr", DistributorAddr);
                            result.Add("CLCustName", CLCustName);
                            result.Add("ECode", EncLink);
                            result.Add("DateStart", DateStart);
                            result.Add("DateEnd", DateEnd);
                            result.Add("DiscType", DiscType);
                            result.Add("PrevCL", PrevCL);

                        }
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ShowQuantity(string CLNum) {
            int result = 0;
            string[] tmp = CLNum.Split('/');
            string Channel = tmp[1].ToString();

            if (!(Channel == "HOS" || Channel == "INS" || Channel == "PHA")){
                result = 1;
            }

            return result;
        }

        private static string LoadCLProducts(string CLNum) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_RequestProduct.CLNum, " +
                "tbl_RequestProduct.ProductCode, " +
                "tbl_RequestProduct.ProductName, " + 
                "tbl_RequestProduct.Discount, " + 
                "tbl_RequestProduct.Quantity " + 
                "FROM " + dbName + ".dbo.tbl_RequestProduct " +
                "WHERE (tbl_RequestProduct.CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        int idx = 0;
                        while (rdr.Read()){
                            idx++;

                            string ProductCode = rdr.GetValue(1).ToString();
                            string ProductName = rdr.GetValue(2).ToString();
                            string Discount = rdr.GetValue(3).ToString();
                            string Quantity = rdr.GetValue(4).ToString();

                            string TmpProd = "<tr>";
                            TmpProd += "<td align='center'>" + idx.ToString() + "</td>";
                            TmpProd += "<td colspan='5' align='left'>" + ProductName + "</td>";
                            TmpProd += "<td colspan='2' align='right'>" + Discount + "</td>";
                            if (ShowQuantity(CLNum) == 1) {
                                TmpProd += "<td align='right'>" + Quantity + "</td>";
                            }
                            TmpProd += "</tr>";

                            result += TmpProd;
                        }
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string LoadCLApvList(string CLNum) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_RequestApproval.CLNum, " +
            "tbl_RequestApproval.StepSeq, " +
            "UserAccounts.FullName, " +
            "mstRole.RoleAlias " +
            "FROM " + dbName + ".dbo.tbl_RequestApproval " + 
            "INNER JOIN " + dbName + ".dbo.UserAccounts AS UserAccounts " + 
            "ON UserAccounts.UserName = tbl_RequestApproval.CurrentStep " + 
            "INNER JOIN " + dbName + ".dbo.mstRole " + 
            "ON mstRole.IDRole = UserAccounts.IDRole " + 
            "WHERE (tbl_RequestApproval.CLNum = @CLNum) " + 
            "ORDER BY tbl_RequestApproval.StepSeq DESC";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        int idx = 0;
                        while (rdr.Read()){
                            idx++;

                            string StepSeq = rdr.GetValue(1).ToString();
                            string ApvName = rdr.GetValue(2).ToString();
                            string ApvRole = rdr.GetValue(3).ToString();

                            string tmpApv = "";
                            if (idx == 1){
                                tmpApv = "<p align='left'>" + ApvName + "<br />" +
                                    ApvRole + "</p>" + "<p style='color:#fff;'>.</p>" + 
                                    "<p align='left'>CC:</p><p align='left'>";
                            }else{
                                tmpApv = "Approved by " + ApvName + " - " + ApvRole + "<br />";
                            }

                            result += tmpApv;
                        }
                        result += "</p>";
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string LoadCLCustomers(string CLNum){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_RequestCustomer.CLNum, " +
                "tbl_RequestCustomer.CustCode, " + 
                "tbl_RequestCustomer.CSCodeAZI, " + 
                "tbl_RequestCustomer.CustName " +
                "FROM " + dbName + ".dbo.tbl_RequestCustomer " +
                "WHERE (tbl_RequestCustomer.CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        int idx = 0;

                        result = "<table border='1' cellpadding='3' cellspacing='0'>" +
                        "<tr bgcolor='#CCC'><td align='center'>No</td>" + 
                        "<td align='center'>AZI Cust Code</td>" + 
                        "<td align='center'>Dist Cust Code</td>" + 
                        "<td align='center' colspan='5'>Customer Name</td></tr>";
                        while (rdr.Read()){
                            idx++;

                            string CustCode = rdr.GetValue(1).ToString();
                            string CSCodeAZI = rdr.GetValue(2).ToString();
                            string CustName = rdr.GetValue(3).ToString();

                            string TmpCust = "<tr>";
                            TmpCust += "<td align='center'>" + idx.ToString() + "</td>" + 
                            "<td>" + CustCode + "</td>" +
                            "<td>" + CSCodeAZI + "</td>" +
                            "<td colspan='5'>" + CustName + "</td>" + 
                            "</tr>";

                            result += TmpCust;
                        }
                        result += "</table>";
                        result += "<p style='color:#fff;'>.</p>";
                        result += "<p style='color:#fff;'>.</p>";
                        result += "<p style='color:#fff;'>.</p>";
                        result += "<p style='color:#fff;'>.</p>";
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string EncodeToken(string CLNum) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.Token FROM " + dbName + ".dbo.tbl_Request " +
                "WHERE (tbl_Request.CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetReqCode(string CLNum)
        {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.CreatedBy FROM " + dbName + ".dbo.tbl_Request " +
                "WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        public static string GenerateTable(string CLNum) {
            string result = "";

            Dictionary<string, string> DataMain = LoadCLData(CLNum);
            string StrPeriod = DataMain["DateStart"] + " - " + DataMain["DateEnd"];
            DataMain["Period"] = StrPeriod;

            
            result = "<html><head></head><body><table id='tblCL' border='0' cellpadding='1' cellspacing='0'>" + 
            "        <tr style='color:#fff;'>" +
            "            <td>.</td>" +
            "            <td colspan='5'>&nbsp;</td>" +
            "        </tr>";
            result += "<tr><td colspan='6' align='left'>Jakarta, " + DataMain["DateRequest"] + "</td></tr>" +
            "        <tr><td align='left' colspan='6'>Ref No : " + CLNum + "</td></tr>" +
            "        <tr><td colspan='6' align='left'>" + DataMain["DistributorName"] + "</td></tr>" +
            "        <tr><td colspan='6' align='left'>" + DataMain["DistributorAddr"] + "</td></tr>" +
            "        <tr><td colspan='6' style='color:#fff;'>.</td></tr>" +
            "        <tr><td colspan='6'>Attn: " + DataMain["DistributorPICName"] + "</td></tr>" +
            "        <tr><td colspan='6' style='color:#fff;'>.</td></tr>" +
            "        <tr><td colspan='6' align='left'>Re: Discount Product AZI in " + DataMain["CLCustName"] + "</td></tr>" +
            "        <tr><td colspan='6' style='color:#fff;'>.</td></tr>" +
            "        <tr><td colspan='6' align='left'>Dear " + DataMain["DistributorPIC"] + "</td></tr>" +
            "        <tr><td colspan='6' style='color:#fff;'>.</td></tr>" +
            "        <tr><td colspan='6' align='left'>Please be informed that we will provide discount for products as listed below:</td></tr>" +
            "       </table>" +
            "<div style='margin:5px 0;'>&nbsp;</div>" +
            "<table border='1' cellpadding='1' cellspacing='0'>" +
            "    <tr bgcolor='#CCC' style='text-align:center;'>" +
            "        <td>No</td><td colspan='5'>Product Name</td><td colspan='2'>Disc</td>";
            if (ShowQuantity(CLNum) == 1) {
                result += "<td>Qty</td>";
            }
            result += "    </tr>" + LoadCLProducts(CLNum) + "</table>" + 
            "<p align='left'>Period: " + DataMain["Period"] + "</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p align='left'>We will inform you immediatelly if there any further changes. Thank you in advance for your attention.</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p align='left'>Kind Regards,</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p>" + LoadCLApvList(CLNum) + "</p>" +
            "<p style='color:#fff;'>.</p>" +
            "<p align='left'>This is a system generated letter, no signature required</p>";
            result += "</body></html>";

            return result;
        }

        public void DownloadZIP(string CLNum){
            string sFileName = SetFileName(CLNum);

            string imagepath = Server.MapPath("~/images/");

            //Create PDF document
            //Response.Clear();
            //Response.BufferOutput = false;
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            StringReader reader = new StringReader(GenerateTable(CLNum));
            Document doc = new Document(PageSize.A4);
            HTMLWorker parser = new HTMLWorker(doc);

            byte[] PDFBytes;
            using (MemoryStream mem = new MemoryStream()){
                PdfWriter PDFWriter = PdfWriter.GetInstance(doc, mem);

                doc.Open();
                doc.AddAuthor("http://akangcupez.com");
                doc.AddCreator("Ebiz v1.0.1");

                iTextSharp.text.Image png = iTextSharp.text.Image.GetInstance(imagepath + "/logoaz.png");
                doc.Add(png);
                parser.Parse(reader);
                doc.NewPage();
                StringReader rdr = new StringReader(LoadCLCustomers(CLNum));
                parser.Parse(rdr);
                BarcodeQRCode qrcode = new BarcodeQRCode(GetReqCode(CLNum), 128, 128, null);
                iTextSharp.text.Image qrImg = qrcode.GetImage();
                qrImg.ScaleAbsolute(128, 128);
                doc.Add(qrImg);
                doc.Close();

                PDFBytes = mem.ToArray();
            }

            Response.Clear();
            Response.BufferOutput = false;
            Response.ContentType = "application/zip";
            Response.AddHeader("Content-Disposition", "filename=" + sFileName + ".zip");

            using (ZipFile zip = new ZipFile()){
                zip.Password = "P455w0rd";
                zip.Encryption = EncryptionAlgorithm.PkzipWeak;
                
                zip.AddEntry(sFileName + ".pdf", PDFBytes);
                zip.AddEntry(sFileName + ".txt", GetTXT(CLNum), Encoding.UTF8);

                zip.Save(Response.OutputStream);
            }
            Response.Close();
        }

        private static string GetTXT(string CLNum){
            string result = "";

            string Currency = "IDR";
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.CLNum, tbl_Request.DiscType, " +
                "CONVERT(varchar,tbl_Request.DateStart,101) AS DateStart, " +
                "CONVERT(varchar,tbl_Request.DateEnd,101) AS DateEnd, " +
                "tbl_Request.DistributorCode, tbl_Request.PrevCL, " +
                "tbl_RequestProduct.ProductCode, " +
                "CONVERT(varchar,(CONVERT(decimal(5,2),tbl_RequestProduct.Discount,101)),101) AS Discount, " +
                "(SELECT TOP 1 tbl_RequestCustomer.CSCodeAZI FROM " + dbName + ".dbo.tbl_RequestCustomer " +
                "WHERE tbl_RequestCustomer.CLNum = @CLNumCust ORDER BY tbl_RequestCustomer.IDSeq ASC) " + 
                "AS CustCode " +
                "FROM " + dbName + ".dbo.tbl_Request " +
                "INNER JOIN " + dbName + ".dbo.tbl_RequestProduct " +
                "ON tbl_RequestProduct.CLNum = tbl_Request.CLNum " + 
                "WHERE (tbl_Request.CLNum = @CLNum)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNumCust", CLNum);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        while (rdr.Read()) {
                            string DiscType = rdr.GetValue(1).ToString();
                            string DateStart = rdr.GetValue(2).ToString();
                            string DateEnd = rdr.GetValue(3).ToString();
                            string DistributorCode = rdr.GetValue(4).ToString();
                            string _PrevCL = rdr.GetValue(5).ToString();
                            string PrevCL = (string.IsNullOrEmpty(_PrevCL)) ? "," : _PrevCL;
                            string ProductCode = rdr.GetValue(6).ToString();
                            string Discount = rdr.GetValue(7).ToString();
                            string CustCode = rdr.GetValue(8).ToString();


                            string tmpResult = DiscType + "," + CLNum + "," + DateStart + "," + DateEnd + "," +
                            Currency + ",," + CustCode + "," + DistributorCode + ",," + ProductCode + "," +
                            Discount + "," + PrevCL;

                            result += tmpResult + System.Environment.NewLine;
                        }
                    }
                }catch(Exception ex){
                    string ErrMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string SetFileName(string CLNum) {
            return CLNum.Replace('/', '-');
        }
    }
}